using System;
using System.Data;
using System.Data.SqlClient;
using DataAccessLayer;
using EntityObjectLayer;

namespace BusinessLogicLayer
{
    public class BLManagePupilInfo
    {
        private readonly BLBehaviour blb = new BLBehaviour();
        private readonly BLPupilReport blpr = new BLPupilReport();

        #region Add New Pupil

        /// <summary>
        /// thuc hien chuc nang nhap them hoc sinh moi vao
        /// </summary>
        /// <param name="bepupil"></param>
        /// <returns></returns>
        public int addNewPupil(BEPupil bepupil)
        {
            //@PupilID char(10),
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.Char, 50);
            //@FullName nvarchar(50),
            SqlParameter FullName = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
            //@classID char(10),
            SqlParameter classID = new SqlParameter("@classID", SqlDbType.Char, 50);
            //@Gender bit,
            SqlParameter Gender = new SqlParameter("@Gender", SqlDbType.Bit);
            //@Birth datetime,
            SqlParameter Birth = new SqlParameter("@Birth", SqlDbType.DateTime);

            SqlParameter BirthPlace = new SqlParameter("@BirthPlace", SqlDbType.NVarChar, 50);

            //@NativePlace nvarchar(50),
            SqlParameter NativePlace = new SqlParameter("@NativePlace", SqlDbType.NVarChar, 50);
            //@Address nvarchar(50),
            SqlParameter Address = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
            //@Phone varchar(50),
            SqlParameter Phone = new SqlParameter("@Phone", SqlDbType.VarChar, 50);
            //@EthnicGroup nchar(10),
            SqlParameter EthnicGroup = new SqlParameter("@EthnicGroup", SqlDbType.NChar, 10);
            //@FatherName nvarchar(50),
            SqlParameter FatherName = new SqlParameter("@FatherName", SqlDbType.NVarChar, 50);
            //@FatherJob nvarchar(50),
            SqlParameter FatherJob = new SqlParameter("@FatherJob", SqlDbType.NVarChar, 50);
            //@FatherBirth int,
            SqlParameter FatherBirth = new SqlParameter("@FatherBirth", SqlDbType.Int);
            //@MotherName nvarchar(50),
            SqlParameter MotherName = new SqlParameter("@MotherName", SqlDbType.NVarChar, 50);
            //@MotherJob nvarchar(50),
            SqlParameter MotherJob = new SqlParameter("@MotherJob", SqlDbType.NVarChar, 50);
            //@MotherBirth int,
            SqlParameter MotherBirth = new SqlParameter("@MotherBirth", SqlDbType.Int);
            //@ParentEmail varchar(30),
            SqlParameter ParentEmail = new SqlParameter("@ParentEmail", SqlDbType.VarChar, 50);
            //@EnterDate datetime,
            SqlParameter EnterDate = new SqlParameter("@EnterDate", SqlDbType.DateTime);
            
            //@Status nvarchar(50),
            SqlParameter Status = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            //@Picture varchar(100)
            SqlParameter Picture = new SqlParameter("@Picture", SqlDbType.VarChar, 50);

            PupilID.Value = bepupil.sPupilID;
            FullName.Value = bepupil.sFullName;
            classID.Value = bepupil.sClassID;
            Gender.Value = bepupil.bGender;
            Birth.Value = bepupil.daBirth;
            BirthPlace.Value = bepupil.sBirthPlace;
            NativePlace.Value = bepupil.sNativePlace;
            Address.Value = bepupil.sAddress;
            Phone.Value = bepupil.sPhone;
            EthnicGroup.Value = bepupil.sEthnicGroup;
            FatherName.Value = bepupil.sFatherName;
            FatherJob.Value = bepupil.sFatherJob;
            FatherBirth.Value = bepupil.iFatherAge;
            MotherName.Value = bepupil.sMotherName;
            MotherJob.Value = bepupil.sMotherJob;
            MotherBirth.Value = bepupil.iMotherAge;
            ParentEmail.Value = bepupil.sParentEmail;
            EnterDate.Value = bepupil.daEnterDate;
            Status.Value = bepupil.sStatus;
            Picture.Value = bepupil.sPicLink;
            try
            {
                Common.Execute("pr_tblPupil_Insert", PupilID, FullName, classID, Gender, Birth, BirthPlace,
                               NativePlace, Address, Phone, EthnicGroup, FatherName, FatherJob, FatherBirth, MotherName,
                               MotherJob, MotherBirth, ParentEmail, EnterDate, Status, Picture);
                for (int i = 0; i < 9; i++)
                {
                    int m = (i + 9)%12;
                    if (Convert.ToInt32(m) == 0)
                    {

                        m = 12;
                    }
                    blb.createBehaviour(bepupil.sPupilID.Value,m);
                }
                blpr.createReport(bepupil);
                return 1;
            }
            catch
            {
                return 0;
            }
        }

        #endregion

        #region Edit Pupil Profile

        /// <summary>
        /// edit pupil profile
        /// </summary>
        /// <param name="bepupil"></param>
        /// <returns></returns>
        public int editPupilProfile(BEPupil bepupil)
        {
            //@PupilID char(10),
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.Char, 10);
            //@FullName nvarchar(50),
            SqlParameter FullName = new SqlParameter("@FullName", SqlDbType.VarChar, 50);
            //@classID char(10),
            SqlParameter classID = new SqlParameter("@classID", SqlDbType.Char, 10);
            //@Gender bit,
            SqlParameter Gender = new SqlParameter("@Gender", SqlDbType.Bit);
            //@Birth datetime,
            SqlParameter Birth = new SqlParameter("@Birth", SqlDbType.DateTime);
            SqlParameter BirthPlace = new SqlParameter("@BirthPlace", SqlDbType.NVarChar,50);
            //@NativePlace nvarchar(50),
            SqlParameter NativePlace = new SqlParameter("@NativePlace", SqlDbType.NVarChar, 50);
            //@Address nvarchar(50),
            SqlParameter Address = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
            //@Phone varchar(50),
            SqlParameter Phone = new SqlParameter("@Phone", SqlDbType.VarChar, 50);
            //@EthnicGroup nchar(10),
            SqlParameter EthnicGroup = new SqlParameter("@EthnicGroup", SqlDbType.NChar, 10);
            //@FatherName nvarchar(50),
            SqlParameter FatherName = new SqlParameter("@FatherName", SqlDbType.NVarChar, 50);
            //@FatherJob nvarchar(50),
            SqlParameter FatherJob = new SqlParameter("@FatherJob", SqlDbType.NVarChar, 50);
            //@FatherBirth int,
            SqlParameter FatherBirth = new SqlParameter("@FatherBirth", SqlDbType.Int);
            //@MotherName nvarchar(50),
            SqlParameter MotherName = new SqlParameter("@MotherName", SqlDbType.NVarChar, 50);
            //@MotherJob nvarchar(50),
            SqlParameter MotherJob = new SqlParameter("@MotherJob", SqlDbType.NVarChar, 50);
            //@MotherBirth int,
            SqlParameter MotherBirth = new SqlParameter("@MotherBirth", SqlDbType.Int);
            //@ParentEmail varchar(30),
            SqlParameter ParentEmail = new SqlParameter("@ParentEmail", SqlDbType.VarChar, 50);
            //@EnterDate datetime,
            SqlParameter EnterDate = new SqlParameter("@EnterDate", SqlDbType.DateTime);
            //@Status nvarchar(50),
            SqlParameter Status = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            //@Picture varchar(100)
            SqlParameter Picture = new SqlParameter("@Picture", SqlDbType.VarChar, 50);

            PupilID.Value = bepupil.sPupilID;
            FullName.Value = bepupil.sFullName;
            classID.Value = bepupil.sClassID;
            Gender.Value = bepupil.bGender;
            Birth.Value = bepupil.daBirth;
            BirthPlace.Value = bepupil.sBirthPlace;
            NativePlace.Value = bepupil.sNativePlace;
            Address.Value = bepupil.sAddress;
            Phone.Value = bepupil.sPhone;
            EthnicGroup.Value = bepupil.sEthnicGroup;
            FatherName.Value = bepupil.sFatherName;
            FatherJob.Value = bepupil.sFatherJob;
            FatherBirth.Value = bepupil.iFatherAge;
            MotherName.Value = bepupil.sMotherName;
            MotherJob.Value = bepupil.sMotherJob;
            MotherBirth.Value = bepupil.iMotherAge;
            ParentEmail.Value = bepupil.sParentEmail;
            EnterDate.Value = bepupil.daEnterDate;
            Status.Value = bepupil.sStatus;
            Picture.Value = bepupil.sPicLink;
            try
            {
                Common.Execute("pr_tblPupil_Update", PupilID, FullName, classID, Gender, Birth, BirthPlace,
                               NativePlace, Address, Phone, EthnicGroup, FatherName, FatherJob, FatherBirth, MotherName,
                               MotherJob, MotherBirth, ParentEmail, EnterDate, Status, Picture);
                blpr.setInfo(bepupil.sPupilID.Value, bepupil);
                return 1;
            }
            catch
            {
                return 0;
            }
        }

        #endregion

        #region Check Pupil

        /// <summary>
        /// thuc hien viec liem tra xem thong tin sinh vien da dang ki co ton tai hay ko
        /// </summary>
        /// <param name="bepupil"></param>
        /// <returns></returns>
        public int chekPupilExist(BEPupil bepupil)
        {
            //@FullName nvarchar(50),
            SqlParameter Fullname = new SqlParameter("@FullName", SqlDbType.NVarChar, 50);
            //@Gender bit,
            SqlParameter Gender = new SqlParameter("@Gender", SqlDbType.Bit);
            //@Birth datetime,
            SqlParameter Birth = new SqlParameter("@Birth", SqlDbType.DateTime);
            //@NativePlace nvarchar(50),
            SqlParameter NativePlace = new SqlParameter("@NativePlace", SqlDbType.NVarChar,50);
            //@Address nvarchar(50),
            SqlParameter Address = new SqlParameter("@Address", SqlDbType.NVarChar,50);
            //@FatherName nvarchar(50),
            SqlParameter FatherName = new SqlParameter("@FatherName", SqlDbType.NVarChar,50);
            //@MotherName nvarchar(50),

            SqlParameter MotherName = new SqlParameter("@MotherName", SqlDbType.NVarChar,50);

            Fullname.Value = bepupil.sFullName;
            Gender.Value = bepupil.bGender;
            Birth.Value = bepupil.daBirth;
            NativePlace.Value = bepupil.sNativePlace;
            Address.Value = bepupil.sAddress;
            FatherName.Value = bepupil.sFatherName;
            MotherName.Value = bepupil.sMotherName;

            SqlDataReader dr = Common.GetFromDataReader("pr_tblPupil_SelectOne", Fullname, Gender, Birth, NativePlace,
                                                        Address, FatherName, MotherName);
            if (dr.HasRows) return 1;
            else return 0;
        }

        #endregion

        #region Load Class

        /// <summary>
        /// Load All class for binding
        /// </summary>
        /// <returns></returns>
        public DataSet SelectAllClass()
        {
            DataTable dt = Common.GetFromDataTable("pr_tblClass_SelectName");
            return dt.DataSet;
        }

        #endregion

        #region Load the last student

        /// <summary>
        /// Loat the last  Pupil ID's
        /// </summary>
        /// <returns></returns>
        public String loadLastPupilID(string g)
        {
            SqlParameter get=new SqlParameter("@Get",SqlDbType.Char,5);
            get.Value = g;
            String str = Common.GetString("pr_tblPupil_SelectTop",get);
            return str;
        }

        #endregion

        #region Search the Pupil List With Parametter

        /// <summary>
        /// Search the Pupil List With Parametter
        /// </summary>
        /// <param name="bep"></param>
        /// <returns></returns>
        public DataSet searchPupilProfile(BEPupil bep)
        {
            
            DataTable dt;
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.VarChar, 50);
            SqlParameter PupilName = new SqlParameter("@FullName", SqlDbType.VarChar, 50);
            SqlParameter PupilClass = new SqlParameter("@ClassID", SqlDbType.VarChar, 50);

            PupilID.Value = bep.sPupilID;
            PupilName.Value = bep.sFullName;
            PupilClass.Value = bep.sClassID;
            try
            {
                dt = Common.GetFromDataTableWithPaging("pr_tblPupil_SearchAll", 0, 10, PupilID, PupilName, PupilClass);
            }
            catch (Exception e)
            {
                throw e;
            }
            
            return dt.DataSet;
        }

        #endregion

        #region Load Pupil Profile

        /// <summary>
        /// Load Profile of one Pupil
        /// </summary>
        /// <param name="bep"></param>
        /// <returns></returns>
        public DataSet loadPupilProfile(BEPupil bep)
        {
            DataTable dt;
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.VarChar, 50);
            PupilID.Value = bep.sPupilID;
            dt = Common.GetFromDataTable("pr_tblPupil_SelectAll", PupilID);
            return dt.DataSet;
        }

        #endregion

        #region Set Status for Pupil

        /// <summary>
        /// Set Status for Pupil
        /// </summary>
        /// <param name="p"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        public int setStatus(string p, string status)
        {
            SqlParameter PupilID = new SqlParameter("@PupilID", SqlDbType.Char, 10);
            SqlParameter Status = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            PupilID.Value = p;
            Status.Value = status;
            return Common.Execute("pr_tblPupil_SetStatus", PupilID, Status);
        }

        #endregion

        #region Count Number of Pupil

        public int getPupilCount(string c)
        {
            SqlParameter Class = new SqlParameter("@Class", SqlDbType.Char, 10);
            Class.Value = c;
            return Convert.ToInt32(Common.GetFromDataTable("pr_tblClass_SelectCountPupil", Class).Rows[0][0].ToString());
        }

        #endregion

        #region Get Class by Type

        public DataSet getClassByType(string c)
        {
            SqlParameter Class = new SqlParameter("@Class", SqlDbType.Char, 10);
            Class.Value = c;
            DataTable dt = Common.GetFromDataTable("pr_tblClass_SelectByType", Class);
            return dt.DataSet;
        }

        #endregion

        #region Get Pupil By Status

        public DataSet getPupilByStatus(string s)
        {
            SqlParameter Status = new SqlParameter("@Status", SqlDbType.NVarChar, 50);
            Status.Value = s;
            DataTable dt= Common.GetFromDataTable("pr_tblPupil_SelectByStatus", Status);
            return dt.DataSet;
        }

        #endregion


        #region Switch Class


        public int switchClass(string p,string c) 
        {
            SqlParameter Class = new SqlParameter("@Class", SqlDbType.Char, 10);
            Class.Value = c;
            SqlParameter Pupil = new SqlParameter("@Pupil", SqlDbType.Char, 10);
            Pupil.Value = p;
            return Common.Execute("pr_tblPupil_switchClass", Pupil, Class);
        }

        #endregion


    }  
}

